package com.yj.web.dao.mapper;

import com.yj.web.dao.po.admin.Area;
import com.yj.web.dao.po.admin.Department;
import com.yj.web.dao.po.admin.Duty;
import com.yj.web.dao.po.admin.Employee;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface EmployeeMapper {
    @Select("select employee.id, name, password, employee.number, phone, email, duty, department, area, city, role_number as roleNumber," +
            "is_active as isActive, manager, create_time as createTime, update_time as updateTime, role.role as roleName from employee left join role on employee.role_number = role.number where employee.id = #{id} and employee.deleted = 0 ")
    public Employee findEmployeeById(@Param("id") long id);

    @Insert("insert into employee (name, password, number, phone, email, duty, department, area, city, role_number, is_active, manager," +
            "create_time, update_time) " +
            "values (#{employee.name}, #{employee.password}, #{employee.number}, #{employee.phone}, #{employee.email}, " +
            "#{employee.duty}, #{employee.department}, #{employee.area}, #{employee.city}, #{employee.roleNumber}, " +
            "#{employee.isActive}, #{employee.manager}, now(), now())")
    @Options(useGeneratedKeys = true, keyProperty = "employee.id")
    public long insertEmployee(@Param("employee") Employee employee);

    @Select("select employee.id, name, employee.number, phone, email, duty, department, area, city, role_number as roleNumber, is_active as isActive, manager, create_time as createTime, update_time as updateTime, role.role as roleName " +
            "from employee, role where phone = #{phone} and employee.deleted = 0 and employee.role_number = role.number")
    public Employee findEmployeeByPhone(@Param("phone") String phone);

    @Select("select employee.id, name, employee.number, phone, email, duty, department, area, city, role_number as roleNumber, is_active as isActive, manager, create_time as createTime, update_time as updateTime, role.role as roleName from employee, role where phone = #{phone} and password = #{password} and employee.deleted = 0 and employee.role_number = role.number")
    public Employee findEmployeeByPhoneAndPassword(@Param("phone") String phone, @Param("password") String passwd);

    @Update("update employee set name=#{employee.name}, number=#{employee.number}, phone=#{employee.phone}, " +
            " email=#{employee.email}, duty=#{employee.duty}, department=#{employee.department}, area=#{employee.area}, city=#{employee.city}, " +
            " role_number=#{employee.roleNumber}, is_active=#{employee.isActive}, manager=#{employee.manager}," +
            " update_time=now() where id = #{employee.id}")
    public boolean updateEmployeeInfoById(@Param("employee") Employee employee);

    @Update("update employee set password=#{employee.password} where id = #{employee.id}")
    public boolean resetPassword(@Param("employee") Employee employee);

//    @Select("select * from employee where email = #{email}")
//    public Employee findEmployeeByEmail(String email);

//    @Select("select * from employee where deleted = 0 order by role_number asc, id asc limit #{size} offset #{offset}")
//    public List<Employee> listEmployee(@Param("offset") int offset, @Param("size") int size);

//    @Select("select count(*) from employee where deleted = 0")
//    public int countEmployee();

    @Select("select * from employee where number = #{number} and deleted = 0")
    public Employee searchByNumber(@Param("number") String  number);

    @Delete("update employee set deleted = 1 where id = #{id}")
    public int deleteById(@Param("id") long id);

//    @Select("select * from employee where instr(name, #{keyword}) order by id asc")
//    public List<Employee> listEmployeeLikeName(@Param("keyword") String keyword);

    @Select("<script>" +
            "select employee.id, name, employee.number as number, phone, email, duty, department, area, city, role_number as roleNumber, is_active as isActive, manager, " +
            "create_time as createTime, update_time as updateTime, role.role as roleName from employee left join role on employee.role_number = role.number " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"name != ''\">" +
            "instr(name, #{name}) " +
            "</if>" +
            "<if test = \"department != ''\">" +
            "and department = #{department} " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(phone, #{phone}) " +
            "</if>" +
            "<if test = \"offset != -1\">" +
            "and employee.deleted = 0 " +
            "</if>" +
            "</trim>" +
            "order by employee.create_time desc limit #{size} offset #{offset}" +
            "</script>")
    public List<Employee> listEmployee(@Param("name") String name, @Param("department") String department,
                                       @Param("phone") String phone, @Param("offset") int offset, @Param("size") int size);

    @Select("<script>" +
            "select count(*) from employee" +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"name != ''\">" +
            "instr(name, #{name}) " +
            "</if>" +
            "<if test = \"department != ''\">" +
            "and department = #{department} " +
            "</if>" +
            "<if test = \"phone != ''\">" +
            "and instr(phone, #{phone}) " +
            "</if>" +
            "<if test = \"phone != -1\">" +
            "and employee.deleted = 0 " +
            "</if>" +
            "</trim>" +
            "</script>")
    public int countEmployee(@Param("name") String name, @Param("department") String department,
                                       @Param("phone") String phone);


    @Insert("insert into duty (name) values (#{name})")
    public int insertDuty(@Param("name") String name);

    @Select("select * from duty")
    public List<Duty> listDuty();

    @Insert("insert into department (name) values (#{name})")
    public int insertDepartment(@Param("name") String name);

    @Select("select * from department")
    public List<Department> listDepartment();

    @Insert("insert into area (name) values (#{name})")
    public int insertArea(@Param("name") String name);

    @Select("select * from area")
    public List<Area> listArea();

    //所有业务经理
    @Select("select * from employee where role_number = 3")
    public List<Employee> listManager();

    //所有业务经理下属的业务员
    @Select("select id, name, number, phone, email, duty, department, area, city, role_number as roleNumber, is_active as isActive, manager, create_time as createTime, update_time as updateTime " +
            "from employee where manager = #{managerId} and deleted = 0 and role_number = 4")
    public List<Employee> listEmployeeByManager(@Param("managerId") long managerId);

    @Select("select id from employee where manager = #{managerId} and deleted = 0 and role_number = 4")
    public List<Long> listEmployeeIdByManager(@Param("managerId") long managerId);


    @Select("select id, name, number, phone, email, duty, department, area, city, role_number as roleNumber, is_active as isActive, manager, create_time as createTime, update_time as updateTime from employee where deleted = 0 and role_number in (3,4)")
    public List<Employee> listAllEmployeeByManager();

    @Select("select name from employee where id = #{id}")
    public String getNameById(@Param("id") long id);

    @Update("update employee set is_active = #{isActive} where id = #{id}")
    public boolean updateEmployeeStatus(@Param("id") long id, @Param("isActive") int isActive);
}
